import java.io.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;

public class MoneyKeeperModel {
	private Connection conn = null;
	private String dbName, driver, protocol, connectionURL;
	private ResultSet rs;
	private Statement s;
	private PreparedStatement ps;
	
	private ModifiedTableModel dailyIncomeTableModel;
	private ModifiedTableModel dailyExpenseTableModel;
	private ModifiedTableModel monthlyTableModel;
	private ModifiedTableModel customSummaryTableModel;
	private ModifiedTableModel monthlySummaryTableModel;
	
	private static final String[] MONTHLY_TB_COLUMN_NAME = {"ID","NO","USER", "DATE", "CATEGORY", "TRANSACTIONS", 
			"METHOD", "INCOME","EXPENSE","BALANCE"};
	private static final String[] DAILY_TB_COLUMN_NAME = {"ID","NO","USER","CATEGORY","TRANSACTIONS","AMOUNT"};
	
	private Calendar queryDate;
	private String dateField;
	private int year,month,curr_day,first_day,last_day;
	private String lastDateString, firstDateString, currDateString;
	public static final int INSERT = 1;
	public static final int MODIFY = 2;
	public static final int DELETE = 3;
	
	private String programPath; 
	private File memoFolder, listFolder;
	
	MoneyKeeperModel(){
		//initialize Folders
		programPath = (this.getClass().getResource(".").getPath().replace("/","\\")).substring(1);
		//programPath = programPath.substring(0,programPath.lastIndexOf("MoneyKeeper\\Core\\"));
		//System.out.println(programPath);
		memoFolder = new File(programPath + "Data\\Memo");
		listFolder = new File(programPath + "Data\\List");
		
		//----------------------
		
		queryDate = Calendar.getInstance();
		dbName = programPath + "DB";
		driver = "org.apache.derby.jdbc.EmbeddedDriver";
		protocol = "jdbc:derby:";
		connectionURL = protocol + dbName;
		dailyIncomeTableModel = new ModifiedTableModel(DAILY_TB_COLUMN_NAME,0);
		dailyExpenseTableModel = new ModifiedTableModel(DAILY_TB_COLUMN_NAME,0);
		monthlyTableModel = new ModifiedTableModel(MONTHLY_TB_COLUMN_NAME,0);
		customSummaryTableModel = new ModifiedTableModel(MONTHLY_TB_COLUMN_NAME,0);
		initializeDate();
	}
	
	public void openConnection(){
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager.getConnection(connectionURL);
			s = conn.createStatement();
		} catch (Exception e){}
	}
	
	public void closeConnection(){
		try{
			if(rs!=null) rs.close();
			if(s!=null) s.close();
			if(conn!= null) conn.close();
		}catch(Exception e){}
	}
	
	private void initializeDate(){
		year = queryDate.get(Calendar.YEAR);
		month = queryDate.get(Calendar.MONTH);
		curr_day = queryDate.get(Calendar.DATE);
		queryDate.set(year,month+1,0);
		last_day = queryDate.get(Calendar.DATE);
		queryDate.set(year, month, curr_day);
		lastDateString = "'" + year +"-" + ((month+1<10)? "0"+(month+1):month+1) + "-" + ((last_day<10)?"0" + last_day:last_day) + "'";
		firstDateString = "'" + year +"-" + ((month+1<10)? "0"+(month+1):month+1) + "-01" + "'";
		currDateString = "'" + year +"-" + ((month+1<10)? "0"+(month+1):month+1) + "-" + ((curr_day<10)?"0" + curr_day:curr_day) + "'";
		
	}
	
	public void setQueryDate(Calendar q_date){
		queryDate = q_date;
		initializeDate();
	}
	
	public ModifiedTableModel getMonthData(){
		monthlyTableModel.setRowCount(0);		
		
		try{
			openConnection();			
			rs = s.executeQuery("select * from MoneyTable where Trans_date between " + firstDateString + " and " + lastDateString + " order by trans_date");			
			
			int counter = 1;
			float balance = 0;
			while(rs.next()){
				balance = balance + rs.getFloat(7)-rs.getFloat(8);
				balance = (int)(balance * 100)/100;
				
				Object[] rowData = {rs.getString(1), counter, rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
					rs.getString(6),rs.getFloat(7),rs.getFloat(8), balance};
				monthlyTableModel.addRow(rowData);
				counter++;
			
			}
		
		}catch(Exception e){
			e.printStackTrace();
		}
				
		closeConnection();
		return monthlyTableModel;
	}

	public ModifiedTableModel getCustomSummaryData(String start_date, String end_date){
		customSummaryTableModel.setRowCount(0);		
		
		try{
			openConnection();			
			rs = s.executeQuery("select * from MoneyTable where Trans_date between " + start_date+ " and " + end_date + " order by trans_date");			
			
			int counter = 1;
			float balance = 0;
			while(rs.next()){
				balance = balance + rs.getFloat(7)-rs.getFloat(8);
				balance = (int)(balance * 100)/100;
				Object[] rowData = {rs.getString(1), counter, rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
					rs.getString(6),rs.getFloat(7),rs.getFloat(8), balance};
				customSummaryTableModel.addRow(rowData);
				counter++;
			
			}
		
		}catch(Exception e){
			e.printStackTrace();
		}
				
		closeConnection();
		return customSummaryTableModel;
	}
	
	public ModifiedTableModel getDailyIncomeData(){
		dailyIncomeTableModel.setRowCount(0);
		try{
			openConnection();			
			rs = s.executeQuery("select * from MoneyTable where Trans_date = " +currDateString + " and income > 0 order by category");
			
			int counter = 1;
			while(rs.next()){
				Object[] rowData = {rs.getString(1), counter, rs.getString(2), rs.getString(4), rs.getString(5), rs.getFloat(7)};
				dailyIncomeTableModel.addRow(rowData);
				counter++;
			
			}
		
		}catch(Exception e){
			e.printStackTrace();
		}
				
		closeConnection();
		return dailyIncomeTableModel;
	}
	
	public ModifiedTableModel getDailyExpenseData(){
		dailyExpenseTableModel.setRowCount(0);		
		
		try{
			openConnection();			
			rs = s.executeQuery("select * from MoneyTable where Trans_date = " +currDateString + " and expense > 0 order by category");
						
			
			int counter = 1;
			while(rs.next()){
				Object[] rowData = {rs.getString(1), counter, rs.getString(2), rs.getString(4), rs.getString(5),rs.getFloat(8)};
				dailyExpenseTableModel.addRow(rowData);
				counter++;
			}
		
		}catch(Exception e){
			e.printStackTrace();
		}
				
		closeConnection();
		return dailyExpenseTableModel;
	}
	
	public String insertData(String entry_date, String trans_category, String transactions, 
			boolean trans_type, String user, String trans_method, String amount){
		
		try{
		openConnection();
			
			ps = conn.prepareStatement("INSERT INTO MoneyTable (USER_NAME, TRANS_DATE, CATEGORY, TRANSACTIONS, TRANS_METHOD, INCOME,EXPENSE) " +
					"VALUES (?,?,?,?,?,?,?)");
			
			ps.setString(1,user);
			ps.setString(2, entry_date);
			
			ps.setString(3,trans_category);
			ps.setString(4,transactions);
			ps.setString(5,trans_method);
				//incomeChecked
			if(trans_type){
				ps.setString(6,amount);
				ps.setString(7,"0");
			}else{
				ps.setString(6,"0");
				ps.setString(7,amount);
			}
			ps.executeUpdate();	
		}catch(Exception e){e.printStackTrace();
		}finally{closeConnection();}
		
		return "Insert Successful";
	}
		
	public String modifytData(String ID,
			String user, String entry_date,
			String trans_category, String transactions,
			boolean trans_type, String trans_method, 
			String amount){
		try{
			openConnection();
			ps = conn.prepareStatement("UPDATE MoneyTable SET USER_NAME = ?, " +
				"TRANS_DATE = ?, " +
				"CATEGORY = ?," +
				"TRANSACTIONS = ?," +
				"TRANS_METHOD = ?," +
				"INCOME = ?," +
				"EXPENSE = ?" +
				"WHERE ID = ?");

			ps.setString(1, user );
			ps.setString(2, entry_date);
			ps.setString(3, trans_category);
			ps.setString(4, transactions);
			ps.setString(5, trans_method);
			
			if(trans_type){
				ps.setString(6,amount);
				ps.setString(7,"0");
			}else{
				ps.setString(6,"0");
				ps.setString(7,amount);
			}
			ps.setString(8, ID);
			
			ps.executeUpdate();

			
		}catch(Exception e){e.printStackTrace();
		}finally{closeConnection();}
		
		return "Modify Successful";
	}
	
	public String deleteAll(){

		try{
			openConnection();
			ps = conn.prepareStatement("delete from MoneyTable where Trans_date between " + firstDateString + " and " + lastDateString);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		closeConnection();
		return "Delete Successful!";
	}
	public String delete(String ID){

		try{
			openConnection();
			ps = conn.prepareStatement("delete from MoneyTable where ID = ?");
			ps.setString(1, ID);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		closeConnection();
		return "Delete Successful!";
		
	}
	
	public void saveMemo(String memoContent){
		String currFileName = getYrMonth();
		File file= new File(memoFolder.getAbsolutePath() +"\\"+ currFileName + ".dat");
		try{
		BufferedWriter out = new BufferedWriter(new FileWriter(file));
		out.write(memoContent);
		out.close();
		}catch(Exception e){e.printStackTrace();}
	}
	
	public String loadMemo(String yrMonth){
		String currFileName = yrMonth;
		StringBuffer stbr = new StringBuffer();
		File file= new File(memoFolder.getAbsolutePath() +"\\"+ currFileName + ".dat");
		try{
		BufferedReader read = new BufferedReader(new FileReader(file));
		String checker = read.readLine();
		while(checker != null){				
				stbr.append(checker);
				stbr.append("\n");
				checker = read.readLine();				
			}
		read.close();
		}catch(Exception e){};
		
		return stbr.toString();
		
	}
	
	public String getYrMonth(){
		String yrMonth = ""+ year + ((month+1<10)? "0"+(month+1):month+1);
		return yrMonth;
	}
	
	public DefaultListModel loadList(int listMode){
		DefaultListModel list = new DefaultListModel();
		String currFileName;
		
		
		if(listMode  == ListModifier.EXPENSE_LIST){
			currFileName = "EXP_LIST";
		}else if(listMode == ListModifier.INCOME_LIST){
			currFileName = "INC_LIST";
		}else if(listMode == ListModifier.USER_LIST){
			currFileName = "USER_LIST";
		}else{
			currFileName = "METHOD_LIST";
		}
		File file= new File(listFolder.getAbsolutePath() +"\\"+ currFileName + ".dat");
		try{
			BufferedReader read = new BufferedReader(new FileReader(file));
			String item = read.readLine();
			while(item != null){				
					list.addElement(item);
					item = read.readLine();				
				}
			read.close();
			}catch(Exception e){};	
		
		return list;		
	}
	
	public String[] loadListInArrayForm(int listMode){
		Object[] tempArray=loadList(listMode).toArray();
		String[] returnArray = new String[tempArray.length];
		for (int i = 0; i<tempArray.length; i++){
			returnArray[i]= (String) tempArray[i];
		}
		return returnArray;
	}
	
	public void saveList(DefaultListModel list, int listMode){
		String currFileName;
		
		if(listMode  == ListModifier.EXPENSE_LIST){
			currFileName = "EXP_LIST";
		}else if(listMode == ListModifier.INCOME_LIST){
			currFileName = "INC_LIST";
		}else if(listMode == ListModifier.USER_LIST){
			currFileName = "USER_LIST";
		}else{
			currFileName = "METHOD_LIST";
		}
		File file= new File(listFolder.getAbsolutePath() +"\\"+ currFileName + ".dat");
		try{
			BufferedWriter out = new BufferedWriter(new FileWriter(file));
			for(int i=0; i<list.size(); i++){
				out.write((String)list.elementAt(i));
				out.newLine();
				
			}
			out.close();
		}catch(Exception e){e.printStackTrace();}		
	}
	
	
}

